×
☰ See All Chapters

JPA Query Parameters

JPA query supports named parameters and positional parameters for parameterizing the queries. Any queries (Delete, update, select) can be parameterized. Both named parameters and positional parameters cannot be used in a single query, but can be used in different queries. Query A can use named parameters and query B can use positional parameters, but either query A or query B cannot use both.

Named parameters

Named parameters are denoted by prefixing an arbitrary name with a colon in JPQL string. To populate the Query object with parameter values use the method given below:

public Query setParameter(String name, Object value);

Example:

Query q = em.createQuery("SELECT x FROM Student x where x.sid < :studentID and x.address.city = :studentCity");

q.setParameter("studentID", 5);

q.setParameter("studentCity", "Bangalore");

The method setParameter(String name, Object value) returns the same query object, so we can do method chaining while setting the parameter values as below:

Query q = em.createQuery("SELECT x FROM Student x where x.sid < :studentID and x.address.city = :studentCity");

q.setParameter("studentID", 5).setParameter("studentCity", "Bangalore");

To set the value for a parameter of IN clause, value should be a List of values of matching data type.

Query q = em.createQuery("SELECT x FROM Student x where x.sid in :studentID and x.address.city = :studentCity");

q.setParameter("studentID", Arrays.asList(1, 2));

q.setParameter("studentCity", "Bangalore");

Positional parameters

Positional parameters are denoted by using an integer prefixed by a question mark. To populate the Query object with parameter values use the method given below:

public Query setParameter (int pos, Object value);

Example:

Query q = em.createQuery("SELECT x FROM Student x where x.sid < ?1 and x.address.city = ?2");

q.setParameter(1, 5);

q.setParameter(2, "Bangalore");

 

The method setParameter (int pos, Object value)returns the same query object, so we can do method chaining while setting the parameter values as below:

Query q = em.createQuery("SELECT x FROM Student x where x.sid < ?1 and x.address.city = ?2");

q.setParameter(1,5).setParameter(2, "Bangalore");

To set the value for a parameter of IN clause, value should be a List of values of matching data type.

Query q = em.createQuery("SELECT x FROM Student x where x.sid in ?1 and x.address.city = ?2");

q.setParameter(1, Arrays.asList(1, 2));

q.setParameter(2, "Bangalore");

JPA Query Parameters Example

Let us see one example using JPA named and positional parameters in JPA query.

Database script (MySQL)

CREATE TABLE ADDRESS(

AID INT(5) PRIMARY KEY AUTO_INCREMENT,

CITY VARCHAR(30),

ZIPCODE VARCHAR(30)

);                

 

CREATE TABLE STUDENT(

SID INT(5) PRIMARY KEY AUTO_INCREMENT,

SNAME VARCHAR(30),

AID INT(5),

CONSTRAINT FOREIGN KEY (AID) REFERENCES ADDRESS (AID)

);

 

INSERT  INTO ADDRESS(AID,CITY,ZIPCODE) VALUES (1,'Bangalore', '560010');

 

INSERT  INTO STUDENT(SID,SNAME,AID) VALUES (1,'Manu Manjunatha', 1);

INSERT  INTO STUDENT(SID,SNAME,AID) VALUES (2,'Advith Tyagraj', 1);

INSERT  INTO STUDENT(SID,SNAME,AID) VALUES (3,'Likitha', 1);

INSERT  INTO STUDENT(SID,SNAME,AID) VALUES (4,'Tyagraj', 1);

pom.xml

<project xmlns="https://maven.apache.org/POM/4.0.0" xmlns:xsi="https://www.w3.org/2001/XMLSchema-instance"

        xsi:schemaLocation="https://maven.apache.org/POM/4.0.0 https://maven.apache.org/maven-v4_0_0.xsd">

        <modelVersion>4.0.0</modelVersion>

        <groupId>com.java4coding</groupId>

        <artifactId>JPQL_QueryParameterization</artifactId>

        <packaging>jar</packaging>

        <version>1.0-SNAPSHOT</version>

        <name>JPQL_QueryParameterization</name>

        <url>https://maven.apache.org</url>

        <dependencies>

                <dependency>

                        <groupId>junit</groupId>

                        <artifactId>junit</artifactId>

                        <version>3.8.1</version>

                        <scope>test</scope>

                </dependency>

                <dependency>

                        <groupId>org.eclipse.persistence</groupId>

                        <artifactId>javax.persistence</artifactId>

                        <version>2.0.0</version>

                </dependency>

 

                <dependency>

                        <groupId>org.hibernate</groupId>

                        <artifactId>hibernate-entitymanager</artifactId>

                        <version>4.2.8.Final</version>

                </dependency>

 

                <dependency>

                        <groupId>mysql</groupId>

                        <artifactId>mysql-connector-java</artifactId>

                        <version>8.0.11</version>

                </dependency>

        </dependencies>

</project>

 

persistence.xml

<?xml version="1.0" encoding="UTF-8"?>

<persistence xmlns="https://java.sun.com/xml/ns/persistence"

        xmlns:xsi="https://www.w3.org/2001/XMLSchema-instance"

        xsi:schemaLocation="https://java.sun.com/xml/ns/persistence

             https://java.sun.com/xml/ns/persistence/persistence_2_0.xsd"

        version="2.0">

 

        <persistence-unit name="StudentPU">

                <provider>org.hibernate.ejb.HibernatePersistence</provider>

                <properties>

                        <property name="hibernate.connection.url" value="jdbc:mysql://localhost:3306/study" />

                        <property name="hibernate.connection.driver_class" value="com.mysql.cj.jdbc.Driver" />

                        <property name="hibernate.connection.username" value="root" />

                        <property name="hibernate.connection.password" value="root" />

                        <property name="hibernate.archive.autodetection" value="class" />

                        <property name="hibernate.show_sql" value="true" />

                        <property name="hibernate.format_sql" value="true" />

                        <property name="hbm2ddl.auto" value="update" />

                </properties>

        </persistence-unit>

</persistence>

Address.java

package com.java4coding;

 

import javax.persistence.Entity;

import javax.persistence.Id;

import javax.persistence.Table;

 

@Entity

@Table(name = "ADDRESS")

public class Address {

       

        @Id

        private int aid;

       

        private String city;

 

        private String zipcode;

 

        public int getAid() {

                return aid;

        }

        public void setAid(int aid) {

                this.aid = aid;

        }

        public String getCity() {

                return city;

        }

        public void setCity(String city) {

                this.city = city;

        }

        public String getZipcode() {

                return zipcode;

        }

        public void setZipcode(String zipcode) {

                this.zipcode = zipcode;

        }

}

Student.java

package com.java4coding;

 

import javax.persistence.CascadeType;

import javax.persistence.Entity;

import javax.persistence.GeneratedValue;

import javax.persistence.GenerationType;

import javax.persistence.Id;

import javax.persistence.JoinColumn;

import javax.persistence.ManyToOne;

import javax.persistence.Table;

 

@Entity

@Table(name = "STUDENT")

public class Student {

       

        @Id

        @GeneratedValue(strategy = GenerationType.AUTO)

        private int sid;

       

        private String sname;

       

        @ManyToOne(cascade = CascadeType.ALL)

        @JoinColumn(name= "AID")

        private Address address;

 

        public int getSid() {

                return sid;

        }

        public void setSid(int sid) {

                this.sid = sid;

        }

        public String getSname() {

                return sname;

        }

        public void setSname(String sname) {

                this.sname = sname;

        }

        public Address getAddress() {

                return address;

        }

        public void setAddress(Address address) {

                this.address = address;

        }

}

Test.java

package com.java4coding;

 

import java.util.Arrays;

import java.util.List;

 

import javax.persistence.EntityManager;

import javax.persistence.EntityManagerFactory;

import javax.persistence.Persistence;

import javax.persistence.Query;

 

public class Test {

        public static void main(String[] args) {

                EntityManagerFactory emf = Persistence.createEntityManagerFactory("StudentPU");

                EntityManager em = emf.createEntityManager();

 

                em.getTransaction().begin();

 

                Query q = em.createQuery("SELECT x FROM Student x where x.sid < :studentID and x.address.city = :studentCity");

                q.setParameter("studentID", 5);

                //Query q = em.createQuery("SELECT x FROM Student x where x.sid in :studentID and x.address.city = :studentCity");

                //q.setParameter("studentID", Arrays.asList(1, 2));

                q.setParameter("studentCity", "Bangalore");

               

                List<Student> results = (List<Student>) q.getResultList();

               

                for(Student s: results) {

                        System.out.println("~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~" );

                        System.out.println("Student name: " + s.getSname());

                        System.out.println("Student ID: " + s.getSid());

                        System.out.println("Student City: " + s.getAddress().getCity());

                        System.out.println("Student Adress ID: " + s.getAddress().getAid());

                        System.out.println("Student Zipcode: " + s.getAddress().getZipcode());

                        System.out.println("~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~" );

                }

               

                q = em.createQuery("SELECT x FROM Student x where x.sid < ?1 and x.address.city = ?2");

                q.setParameter(1, 5);

                q.setParameter(2, "Bangalore");

               

                results = (List<Student>) q.getResultList();

               

                for(Student s: results) {

                        System.out.println("~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~" );

                        System.out.println("Student name: " + s.getSname());

                        System.out.println("Student ID: " + s.getSid());

                        System.out.println("Student City: " + s.getAddress().getCity());

                        System.out.println("Student Adress ID: " + s.getAddress().getAid());

                        System.out.println("Student Zipcode: " + s.getAddress().getZipcode());

                        System.out.println("~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~" );

                }

               

                em.getTransaction().commit();

        }

}

Project directory structure

jpa-query-parameters-0
 

Output:

Hibernate:

    select

        student0_.sid as sid1_1_,

        student0_.AID as AID3_1_,

        student0_.sname as sname2_1_

    from

        STUDENT student0_ cross

    join

        ADDRESS address1_

    where

        student0_.AID=address1_.aid

        and student0_.sid<?

        and address1_.city=?

Hibernate:

    select

        address0_.aid as aid1_0_0_,

        address0_.city as city2_0_0_,

        address0_.zipcode as zipcode3_0_0_

    from

        ADDRESS address0_

    where

        address0_.aid=?

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Student name: Manu Manjunatha

Student ID: 1

Student City: Bangalore

Student Adress ID: 1

Student Zipcode: 560010

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Student name: Advith Tyagraj

Student ID: 2

Student City: Bangalore

Student Adress ID: 1

Student Zipcode: 560010

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Student name: Likitha

Student ID: 3

Student City: Bangalore

Student Adress ID: 1

Student Zipcode: 560010

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Student name: Tyagraj

Student ID: 4

Student City: Bangalore

Student Adress ID: 1

Student Zipcode: 560010

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Hibernate:

    select

        student0_.sid as sid1_1_,

        student0_.AID as AID3_1_,

        student0_.sname as sname2_1_

    from

        STUDENT student0_ cross

    join

        ADDRESS address1_

    where

        student0_.AID=address1_.aid

        and student0_.sid<?

        and address1_.city=?

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Student name: Manu Manjunatha

Student ID: 1

Student City: Bangalore

Student Adress ID: 1

Student Zipcode: 560010

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Student name: Advith Tyagraj

Student ID: 2

Student City: Bangalore

Student Adress ID: 1

Student Zipcode: 560010

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Student name: Likitha

Student ID: 3

Student City: Bangalore

Student Adress ID: 1

Student Zipcode: 560010

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Student name: Tyagraj

Student ID: 4

Student City: Bangalore

Student Adress ID: 1

Student Zipcode: 560010

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

 

 

All Chapters
Author